package com.ailk.kbi.kbirazor.util;


import groovy.util.logging.Slf4j;

import java.io.ByteArrayOutputStream;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import sun.misc.BASE64Encoder
import jxl.Cell
import jxl.Sheet
import jxl.Workbook
import jxl.biff.formula.StringOperator
import jxl.format.Colour
import jxl.format.UnderlineStyle
import jxl.write.Label
import jxl.write.Number
import jxl.write.NumberFormat
import jxl.write.WritableCellFormat
import jxl.write.WritableFont
import jxl.write.WritableSheet
import jxl.write.WritableWorkbook

@Slf4j
class ExcelUtil {
//	protected static final Log log = LogFactory.getLog(ExcelUtil.class)
	String createExcel(excelXmlString){
//		log.info "创建Excel..."
		def excelXml = new XmlSlurper().parseText(excelXmlString)
		def headerXml = excelXml.header
		def rowsXML = excelXml.result
		int allHeight = Integer.parseInt(headerXml.@height.text())
		ByteArrayOutputStream os = new ByteArrayOutputStream()
		WritableWorkbook wwb = Workbook.createWorkbook(os)
		WritableSheet ws = wwb.createSheet(excelXml.@title.toString(), 0)

//		log.info "zero"
		//创建Excel表头
		addExcelHeader(ws,headerXml.column,allHeight,0,0)
//		log.info "first"
		fillExcelRows(ws,headerXml,rowsXML,allHeight)

		// 写入Exel工作表
		wwb.write()
		// 关闭Excel工作薄对象
		wwb.close()

		(new BASE64Encoder()).encode(os.toByteArray())
	}

	def addExcelHeader(ws,columnsXml,allHeight,x,y){
		columnsXml.each{column->
			String title = column.@title.text()
			int width = Integer.parseInt(column.@width.text())
			int height =  Integer.parseInt(column.@height.text())
			addExcelHeader(ws,title,x,y,x + width -1,y + height -1)


			if(column.column.size() > 0){
				addExcelHeader(ws,column.column, allHeight, x,y + height)
			}
			x = x + width
		}
	}

	def addExcelHeader(ws,title,x1,y1,x2,y2){
		WritableCellFormat cellFormat=new WritableCellFormat()
		cellFormat.setAlignment(jxl.format.Alignment.CENTRE)
		cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE)
		cellFormat.setWrap(true)

		Label label = new Label(x1,y1,title)
		label.setCellFormat(cellFormat)
		ws.addCell(label)
		if(x1 < x2 || y1 < y2){
			ws.mergeCells(x1,y1,x2,y2)
		}
	}

	int iterateFillRows(ws,headerXml,rowsXML,y,level,flatColumnXml,nfPercent,nfInt,nfDouble) {
		level++
		rowsXML.data.each {row->
			if(row != null) {
				flatColumnXml.eachWithIndex {column,index->
					String format = column.@format
					String tab = "";
					for(int i=1; i<level; i++) {
						tab = tab + "　";
					}
					String value = row."${column.@name}".text()
					if(value !=null && value.length() > 0){
//						System.out.print("值：" + value + "  y:" + y + " index:" + index + "    ")
						def cell

						if(format !=null && format.length() > 0){
							if(format.equalsIgnoreCase("percent")) {
								cell = new Number(index,y, new Double(value)/100, nfPercent)
							} else if(format.equalsIgnoreCase("int")){
								cell = new Number(index,y, new Double(value), nfInt)
							}  else if(format.equalsIgnoreCase("intLj")){
								Double d = new Double(value)
								if(d >= 0) {
									cell = new Number(index,y, d, nfInt)
								} else {
									value = "-"
									cell = new Label(index,y,value)
								}
								
							} else if(format.equalsIgnoreCase("flag")) {
								if(value == "0") {
									value = "否";
								} else {
									value = "是";
								}
								cell = new Label(index,y,value);
							} else if(format.equalsIgnoreCase("number")) {
								String dataType = row.@dataType
//								log.info "dt:" + dataType
								if(dataType) {
									if(dataType == "1") {
										cell = new Number(index,y,new Double(value),nfInt)
									} else if(dataType == "2") {
										cell = new Number(index,y,new Double(value),nfDouble)
									} else {
										cell = new Number(index,y,new Double(value)/100,nfPercent)
									}
								}
								else {
									cell = new Number(index,y,new Double(value),nfInt)
								}
								
							}
						} else {
							if(index < 1) {
								value = tab + value;
							} else {

							}

							cell = new Label(index,y,value)
						}
						ws.addCell(cell);
					}
				}
//				System.out.println();
				y++

				if(level <= 4) {
					y = iterateFillRows(ws,headerXml,row,y,level,flatColumnXml,nfPercent,nfInt,nfDouble)
				}
			}
		}
		y
	}

	def fillExcelRows(ws,headerXml,rowsXML,allHeight){
		def flatColumnXml = headerXml.'**'.grep{it.@name != null && it.@name.text().length() > 0}
		//		System.out.println(flatColumnXml.size() + ";" + rowsXML.row.size())
		int y = allHeight
		int level = 0
		WritableCellFormat nfPercent = new WritableCellFormat(new NumberFormat("0.00%"))
		WritableCellFormat nfDouble = new WritableCellFormat(new NumberFormat("0.00"))
		WritableCellFormat nfInt = new WritableCellFormat(new NumberFormat("#,##0"))
		//		WritableCellFormat nfCurrency = new WritableCellFormat(new NumberFormat("#,##0.00"))
		iterateFillRows(ws,headerXml,rowsXML,y,level,flatColumnXml,nfPercent,nfInt,nfDouble)

		//		rowsXML.data.each {row->
		//			flatColumnXml.eachWithIndex {column,index->
		//				//				String format = column.@format
		//				String value = row."${column.@name}".text()
		//				if(value !=null && value.length() > 0){
		//					def cell
		//					//					if(format !=null && format.length() > 0){
		//					//						if(format.equalsIgnoreCase("percent")){
		//					//							cell = new Number(index,y, new Double(value), nfPercent)
		//					//						}
		//					//						else if(format.equalsIgnoreCase("int")){
		//					//							cell = new Number(index,y, new Double(value), nfInt)
		//					//						}
		//					//						else {
		//					//							cell = new Number(index,y, new Double(value), nfCurrency)
		//					//						}
		//					//					}
		//					//					else{
		//					cell = new Label(index,y,value)
		//					//					}
		//
		//					ws.addCell(cell);
		//				}
		//			}
		//			y++
		//
		//			row.data.each { secondrow->
		//				flatColumnXml.eachWithIndex {column,index->
		//					//				String format = column.@format
		//					String value = "　" + secondrow."${column.@name}".text()
		//					if(value !=null && value.length() > 0){
		//						def cell
		//						cell = new Label(index,y,value)
		//						ws.addCell(cell);
		//					}
		//				}
		//				y++
		//			}
		//			//			if(row)
		//		}
	}

}
